/**
 * @FileName: TariffService.java
 * @Author
 * @Description:
 * @Date 2016年9月21日 上午11:31:22
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service;

import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import com.jfinal.plugin.activerecord.Db;

import cn.hpclub.server.util.InterfaceUtil;

public class UserFeedbackService{
    public static UserFeedbackService service = new UserFeedbackService();

    /**
     * @Title: getSqlSelect
     * @Description:TODO
     * @return String
     */

    public String getSqlSelect(){
        String select = "SELECT uf.id,sv.name as gzmk,uf.module_value, uf.name, uf.phone, city, uf.emp_code, ( CASE type WHEN 0 THEN '功能异常' WHEN 1 THEN '优化建议' END ) AS type, "
                + "uf.time, uf.content, ( CASE state WHEN 0 THEN '未处理' WHEN 1 THEN '已处理' END ) AS processState, state, "
                + "( SELECT class_name FROM organization_class oc, organization org WHERE org.WebsiteId = oc.id AND uf.org_id = org.id ) AS className, "
                + "( SELECT DisplayName FROM organization WHERE id = ( SELECT AreaCode FROM organization org WHERE org.id = uf.org_id )) AS AreaName, "
                + "NumCode, DisplayName, IF ( orgType.orgIDTmp || orgType2.orgIDTmp2, '是', '否' ) AS is_accounts_manager ";
        return select;
    }

    /**
     * @Title: getSqlFilter
     * @Description:TODO
     * @param searchInfo
     * @return String
     */

    public String getSqlFilter(ConcurrentHashMap<String, String> searchInfo){
        int topId = InterfaceUtil.getIdTop();
        String sqlExceptSelect = "FROM user_feedback uf LEFT JOIN (select sv.* from sys_code_value sv LEFT JOIN sys_code_type st ON st.id=sv.type_id WHERE st.`code`='GZMK') sv ON uf.module_value = sv.code "
                + " LEFT JOIN (SELECT id AS orgIDTmp FROM `organization` where (parentid=" + topId
                + " OR parentid IN (select id FROM organization WHERE  parentid=" + topId
                + "))) AS orgType ON uf.org_id = orgType.orgIDTmp"
                + " LEFT JOIN (SELECT org.id AS orgIDTmp2, orgcls.WebsiteId AS WebsiteIdType FROM organization org, organization_class orgcls WHERE org.WebsiteId = orgcls.id AND org.enabled=1 AND orgcls.enabled=1 AND ( orgcls.WebsiteId LIKE '6%' OR orgcls.WebsiteId = '1000' OR orgcls.WebsiteId = '4800')) AS orgType2 ON uf.org_id = orgType2.orgIDTmp2"
                + " LEFT JOIN organization org ON uf.org_id = org.id WHERE 1 ";
        sqlExceptSelect += getSqlExceptSelect(searchInfo);
        return sqlExceptSelect;
    }

    /**
     * 获取查询语句
     */
    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = "";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }
        sqlExceptSelect += " ORDER BY state ASC, id DESC ";

        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        if(item.getKey().equals("start_time")){
            return " AND time>='" + item.getValue() + " 00:00:00' ";
        } else if(item.getKey().equals("end_time")){
            return " AND time <='" + item.getValue() + " 23:59:59' ";
        } else if(item.getKey().equals("type")){
            return " AND type=" + item.getValue() + " ";
        } else if(item.getKey().equals("state")){
            return " AND state=" + item.getValue() + " ";
        } else if(item.getKey().equals("gzmk_option")){
            return " AND sv.code='" + item.getValue() + "' ";
        }else{
            return "";
        }
    }

    public void updateState(String id){
        Db.update("UPDATE user_feedback SET state=1 WHERE id=" + id);
    }
}
